package edu.uoc.tdp.pac4.servidor.bbdd;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import edu.uoc.tdp.pac4.common.beans.S2_2Supplier;
import edu.uoc.tdp.pac4.common.beans.S2_2PiecePrice;
import edu.uoc.tdp.pac4.common.exception.*;


public class S2_2SupplierBD {
	

	    public List<S2_2Supplier> obtenerProveedor(int pIdProveedor) throws S2_2ExcSupplier, ExcepcioConnexioBD {	    	
	        List<S2_2Supplier> lista = new ArrayList<S2_2Supplier>();
	        S2_2Supplier prv;
	        Connection connexioBD = null;
	      
	        GestorConnexioBD gestorConnBD = new GestorConnexioBD();

	        try { 
	        	
	        	connexioBD = gestorConnBD.getConnexioBD();
	            Statement sentencia = connexioBD.createStatement();
	            //String select = "SELECT  idproveidor, nom FROM proveidor WHERE idproveidor="+ pIdProveedor;	  ;
	            String select = "SELECT idproveidor,nom,dataalta,percdescompte,quantdescompte FROM proveidor WHERE idproveidor=" + pIdProveedor;
	            ResultSet rs = sentencia.executeQuery(select);	            
	            while (rs.next()) {
	            	prv  = new S2_2Supplier(rs.getInt("idproveidor"),rs.getString("nom"), rs.getDate("dataalta"), rs.getDouble("percdescompte"), rs.getInt("quantdescompte"));
	            	lista.add(prv);				
	            }	           
	            
	            rs.close();
	            sentencia.close();
	        } catch (SQLException sqle) {           
	            throw new S2_2ExcSupplier(S2_2ExcSupplier.ERR_LOAD);          
	        } catch (Exception e) {           
	            throw new  S2_2ExcSupplier(S2_2ExcSupplier.ERR_DESC_EXEC);
	        }
	        return lista;    

	    }

	   
	    public List<S2_2Supplier> consultaProveedores() throws S2_2ExcSupplier, ExcepcioConnexioBD {    
	    	
	        List<S2_2Supplier> lista = new ArrayList<S2_2Supplier>();
	        S2_2Supplier prv;        
	     	        
	        Connection connexioBD = null;
		      
	        GestorConnexioBD gestorConnBD = new GestorConnexioBD();

	        try {
	        	connexioBD = gestorConnBD.getConnexioBD();
	            Statement sentencia = connexioBD.createStatement();
	            String select = "SELECT idproveidor, nom FROM proveidor;";
	            ResultSet rs = sentencia.executeQuery(select);
	            while (rs.next()) {
	                prv = new S2_2Supplier(rs.getInt(1), rs.getString(2));
	                lista.add(prv);
	            }
	            rs.close();
	            sentencia.close();
	        } catch (SQLException sqle) {           
	            throw new S2_2ExcSupplier(S2_2ExcSupplier.ERR_LOAD);          
	        } catch (Exception e) {           
	            throw new  S2_2ExcSupplier(S2_2ExcSupplier.ERR_DESC_EXEC);
	        }
	        return lista;
	    }
	    
	    public List<S2_2PiecePrice> consultaPrecios(int pIdProveedor) throws  S2_2ExcSupplier, ExcepcioConnexioBD {    	
	    	List<S2_2PiecePrice> lista = new ArrayList<S2_2PiecePrice>();	        
	        S2_2PiecePrice prv;	        
	     	        
	        Connection connexioBD = null;
		      
	        GestorConnexioBD gestorConnBD = new GestorConnexioBD();
	       
	        try {
	        	connexioBD = gestorConnBD.getConnexioBD();
	        	
	        	Statement sentencia = connexioBD.createStatement();		      
	            
	            String select = "SELECT proveidor.idproveidor, proveidor.nom as proveedor,peca.idmarca,";
	            select = select + "peca.idtipus, tipuspeca.nom as tipo_pieza, marcacotxe.nom as marca_coche,";
	            select = select + "peca.pvp, proveidor.percdescompte, proveidor.quantdescompte ";
	            select = select + "FROM  tipuspeca,  proveidor,  marcacotxe,  peca "; 
	            select = select + "WHERE peca.idmarca = marcacotxe.idmarca AND "; 
	            select = select + "peca.idproveidor = proveidor.idproveidor AND "; 
	            select = select + "peca.idtipus = tipuspeca.idtipus AND ";            	            
	            select = select + "proveidor.idproveidor=" + pIdProveedor;	            
	           	            
	            ResultSet rs = sentencia.executeQuery(select);           
	            	            
	                  
	            while (rs.next()) {
	                prv = new S2_2PiecePrice(rs.getInt(1), rs.getString(2),rs.getInt(3),
	                		rs.getInt(4), rs.getString(5), rs.getString(6),
	                		rs.getDouble(7), rs.getDouble(8), rs.getInt(9));
	                lista.add(prv);
	            }
	            
	            rs.close();
	            sentencia.close();
	            
	        } catch (SQLException sqle) {	        
	            throw new S2_2ExcSupplier(S2_2ExcSupplier.ERR_LOAD);          
	        } catch (Exception e) { 
	             throw new  S2_2ExcSupplier(S2_2ExcSupplier.ERR_DESC_EXEC);
	        }
	        
	        return lista;
	    }
		   

}
